import pandas as pd
from sqlalchemy import create_engine
import datetime
import openpyxl


def newColumn(col1, col2):
    # lambda表达式中，由多个列的值算出一个新列【注意这里的入参是两个入参数】
    if pd.isnull(col1) and pd.isnull(col2):
        return "普通订单"
    elif pd.isnull(col1) == False:
        return "退款订单"
    elif pd.isnull(col2) == False:
        return "种菜订单"
    return "1"

def convert_percent(value):
    """
    转换字符串百分数为float类型小数
    - 移除 %
    - 除以100转换为小数
    """
    new_value = str(value).replace('%', '')
    return float(new_value) / 100

def deleteDuplicationData(tableName, engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    sql = 'delete from a                                                              '
    sql += '    using '+tableName+' as a, '+tableName+' as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.店铺 = b.店铺 and a.时间=b.时间 )     '
    engine.execute(sql)

# 财务给出的，退款和种菜的订单，需要导入数据库
engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# filePath = 'D:/简尚家居/excel文件/云杉/6月(1).xlsx'
# 财务给出的，日期为横向的
# 拼多多毛利分析 陈天宇v9.xlsx
# 8.3-8.9号周销量毛利表汇总明细8.11-拼多多.xlsx
# 京东-7.27-8.2号周数据分析-梁凯旋-8.19-V2.xlsx
filePath = 'D:/简尚家居/钉钉下载目录/京东-7.27-8.2号周数据分析-梁凯旋-8.19-V2.xlsx'

print('openpyxl开始读取文档:' + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
wb = openpyxl.load_workbook(filePath)
print('openpyxl读取文档完成:' + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
# 获取workbook中所有的表格
sheets = wb.get_sheet_names()
# ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★推广&退货明细★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
df = pd.read_excel(filePath, sheet_name=0)
dfT = df.unstack()
bigDataBlock = 9  # 手工数文档
shopNumber = df.shape[0] - 1  # 总共有多少家店铺
bigDataBlockCellLength = int((df.shape[1] - 1) / bigDataBlock)  # 横着涨的日期个数
dfAll1 = pd.DataFrame(
    columns=['时间', '店铺', '销量（万元）', '推广费用合计（万元）', '不定项（万元）', '售后费用--合计（万元）', '退款费用', '推广费用占比', '不定项占比', '售后占比',
             '退款费用占比'])
bigDataBlockTile = ['销量（万元）', '推广费用合计（万元）', '不定项（万元）', '售后费用--合计（万元）', '退款费用', '推广费用占比', '不定项占比', '售后占比', '退款费用占比']
i = int(dfT.shape[0] / df.shape[0]) - 1
for blockTitle in range(0, len(bigDataBlockTile)):
    # '销量（万元）', '推广费用合计（万元）'数据库的循环
    for i in range(0, shopNumber):
        # 店铺的循环
        monthWeekList = df.iloc[0:1,
                        1 + blockTitle * bigDataBlockCellLength: 1 + bigDataBlockCellLength + blockTitle * bigDataBlockCellLength].values.tolist()[
            0]  #
        # 店铺维度的循环
        tempDf = pd.DataFrame({'时间': monthWeekList})
        # 这个逻辑是????
        shopName = str(df.iloc[1 + i:2 + i, 0:1]).replace(' ', '')
        if '拼多多' in shopName:
            tempDf['店铺'] = shopName[shopName.find('拼多多'):]
        elif '京东' in shopName:
            tempDf['店铺'] = shopName[shopName.find('京东'):]
        tempDf[bigDataBlockTile[blockTitle]] = df.iloc[1 + i:2 + i,
                                               1 + blockTitle * bigDataBlockCellLength: 1 + bigDataBlockCellLength + blockTitle * bigDataBlockCellLength].values.tolist()[
            0]

        dfAll1 = pd.concat([dfAll1, tempDf])

        debug = ''
    debug = ''
debug = ''
# 修复百分号结尾的问题
#dfAll1['推广费用占比'] = dfAll1['推广费用占比'].apply(convert_percent)
#dfAll1['不定项占比'] = dfAll1['不定项占比'].apply(convert_percent)
#dfAll1['售后占比'] = dfAll1['售后占比'].apply(convert_percent)
#dfAll1['退款费用占比'] = dfAll1['退款费用占比'].apply(convert_percent)
dfAll1 = dfAll1.groupby(['店铺', '时间'], as_index=False).agg(
    {"销量（万元）": "sum", "推广费用合计（万元）": "sum", "不定项（万元）": "sum",
     "售后费用--合计（万元）": "sum", "退款费用": "sum", "推广费用占比": "sum",
     "不定项占比": "sum", "售后占比": "sum", "退款费用占比": "sum"})
# 解决可能出现的超时问题bugfix 2020.08.18
con.connection.connection.ping(reconnect=True)
dfAll1.to_sql(name='财务_拼多多_推广退货明细', con=con, if_exists='append', index=False)
deleteDuplicationData('财务_拼多多_推广退货明细', engine)

# ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★毛利明细源数据★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
df = pd.read_excel(filePath, sheet_name=1)
dfT = df.unstack()
bigDataBlock = 4  # 手工数文档
shopNumber = df.shape[0] - 1  # 总共有多少家店铺
bigDataBlockCellLength = int((df.shape[1] - 1) / bigDataBlock)  # 横着涨的日期个数
dfAll2 = pd.DataFrame(
    columns=['时间', '店铺', '销量（万元）', '采购占比', '运费占比', '毛利占比'])
bigDataBlockTile = ['销量（万元）', '采购占比', '运费占比', '毛利占比']
i = int(dfT.shape[0] / df.shape[0]) - 1
for blockTitle in range(0, len(bigDataBlockTile)):
    # '销量（万元）', '推广费用合计（万元）'数据库的循环
    for i in range(0, shopNumber):
        # 店铺的循环
        monthWeekList = df.iloc[0:1,
                        1 + blockTitle * bigDataBlockCellLength: 1 + bigDataBlockCellLength + blockTitle * bigDataBlockCellLength].values.tolist()[
            0]  #
        # 店铺维度的循环
        tempDf = pd.DataFrame({'时间': monthWeekList})
        # 这个逻辑是????
        shopName = str(df.iloc[1 + i:2 + i, 0:1]).replace(' ', '')
        if '拼多多' in shopName:
            tempDf['店铺'] = shopName[shopName.find('拼多多'):]
        elif '京东' in shopName:
            tempDf['店铺'] = shopName[shopName.find('京东'):]
        tempDf[bigDataBlockTile[blockTitle]] = df.iloc[1 + i:2 + i,
                                               1 + blockTitle * bigDataBlockCellLength: 1 + bigDataBlockCellLength + blockTitle * bigDataBlockCellLength].values.tolist()[
            0]

        dfAll2 = pd.concat([dfAll2, tempDf])

        debug = ''
    debug = ''
debug = ''
dfAll2 = dfAll2.groupby(['店铺', '时间'], as_index=False).agg(
    {"销量（万元）": "sum", "采购占比": "sum", "运费占比": "sum",
     "毛利占比": "sum"})
# 解决可能出现的超时问题bugfix 2020.08.18
con.connection.connection.ping(reconnect=True)
dfAll2.to_sql(name='财务_拼多多_毛利明细', con=con, if_exists='append', index=False)
deleteDuplicationData('财务_拼多多_毛利明细', engine)

dfAll = pd.merge(dfAll1, dfAll2, on=['店铺', '时间'], how='left')

# ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★推广和不定项明细★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
dfAll3 = pd.read_excel(filePath, sheet_name=2, header=[1])  # 第一行和第二行做一个联合表头
dfAll3.rename(columns={'Unnamed: 0': '店铺', 'Unnamed: 1': '时间', 'Unnamed: 9': '推广费汇总', 'Unnamed: 16': '销售费用-不定项汇总'},
              inplace=True)
debug = ''

# ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★源数据汇总★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
dfAll = pd.merge(dfAll, dfAll3, on=['店铺', '时间'], how='left')
# 解决可能出现的超时问题bugfix 2020.08.18
con.connection.connection.ping(reconnect=True)
dfAll.to_sql(name='财务_拼多多_推广退货毛利明细汇总分析', con=con, if_exists='append', index=False)
deleteDuplicationData('财务_拼多多_推广退货毛利明细汇总分析', engine)


